package com.csii.adhoc.util.excel;

import com.csii.adhoc.JdbcTemplate.DataSourceConfig;
import com.csii.adhoc.JdbcTemplate.JdbcTemplateFactory;
import com.csii.adhoc.util.StringUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.http.MediaType;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.time.Month;
import java.util.*;

/**
 * @description: excel工具类
 * @author: panChuanJiang
 * @create: 2021-07-26 14:04
 */
@Slf4j
public class ExcelUtils {
   /* public static void main(String[] args) {
        JdbcTemplateFactory factory = new JdbcTemplateFactory(1);
        DataSourceConfig config = new DataSourceConfig();
        //oracle
        config.setUrl("jdbc:oracle:thin:@192.168.20.139:1521:helowin")
                .setUsername("pos").setPasswd("pos")
                .setDriverClass("oracle.jdbc.driver.OracleDriver")
                .setConnId("111");
        JdbcTemplate jdbcTemplate = factory.getBean(config);
        HashMap<String, String> map = new HashMap<>();
        map.put("$UOF_ITEM_SN", "130872275632128");
        exportTemplate("test.xlsx", "D://temp//", map, jdbcTemplate, null);
    }*/

    private static final String YEAR = "$yyyy";
    private static final String MONTH = "$mm";
    private static final String DAY = "$dd";

    public static void exportTemplate(String fileName, String fileDir, Map<String, String> param, JdbcTemplate jdbcTemplate, HttpServletResponse response) {
        try {
            Workbook exl = POIUtils.openOtherExcel(fileDir + fileName);
            Sheet sheet1 = exl.getSheetAt(0);
            List<SqlPosition> positionAndSql = getPositionAndSql(sheet1, param);
            if (positionAndSql.size() == 0) {
                buildExcelDocument(fileName, exl, response);
                return;
            }
            for (int i = 0; i < positionAndSql.size(); i++) {
                SqlPosition sqlPosition = positionAndSql.get(i);
                int rowNum = sqlPosition.getRowIndex();
                int column = sqlPosition.getColumnIndex();
                String sql = sqlPosition.getSql();
                List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
                if (maps.size() == 0) {
                    buildExcelDocument(fileName, exl, response);
                    return;
                }
                if (maps.size() > 1&&positionAndSql.size()>1) {//数据需要往下填充，下移原表格。
                    int starRow = rowNum;
                    sheet1.shiftRows(starRow + 1, sheet1.getLastRowNum(), maps.size() - 1, true, false);
                    if (i != positionAndSql.size() - 1) {
                        for (int j = i + 1; j < positionAndSql.size(); j++) {
                            SqlPosition position = positionAndSql.get(j);
                            if (position.getRowIndex() > rowNum) {
                                position.setRowIndex(position.getRowIndex() + maps.size() - 1);
                            }
                        }
                    }
                }
                boolean first = true;
                for (Map<String, Object> map : maps) {
                    Row row = sheet1.getRow(rowNum);
                    if (row == null) {
                        row = sheet1.createRow(rowNum);
                    }
                    fillRowData(sheet1, map.values(), row, column, sqlPosition, first);
                    first = false;
                    rowNum++;
                }
                if (rowNum <= sheet1.getLastRowNum()) {
                    for (int j = rowNum; j <= sheet1.getLastRowNum(); j++) {
                        POIUtils.refreshRow(sheet1.getRow(j));
                    }
                }
            }
            buildExcelDocument(fileName, exl, response);
        } catch (Exception e) {
            log.error("导出异常", e);
        }
    }

    public static void exportTemplate(String fileName, String fileDir, JdbcTemplate jdbcTemplate, HttpServletResponse response) {
        exportTemplate(fileName, fileDir, new HashMap<>(), jdbcTemplate, response);
    }

    public static void exportTemplate(String fileName, String fileDir, String date, JdbcTemplate jdbcTemplate, HttpServletResponse response) {
        Map<String, String> param = new HashMap<>();
        if (!StringUtils.isEmpty(date) && date.length() == 8) {
            String year = date.substring(0, 4);
            param.put(YEAR, year);
            String month = date.substring(4, 6);
            param.put(MONTH, month);
            String day = date.substring(6, 8);
            param.put(DAY, day);
        }
        exportTemplate(fileName, fileDir, param, jdbcTemplate, response);
    }
    public static void exportTemplate(String fileName, String fileDir, String date,Map<String, String> param, JdbcTemplate jdbcTemplate, HttpServletResponse response) {
        if (!StringUtils.isEmpty(date) && date.length() == 8) {
            String year = date.substring(0, 4);
            param.put(YEAR, year);
            String month = date.substring(4, 6);
            param.put(MONTH, month);
            String day = date.substring(6, 8);
            param.put(DAY, day);
        }
        exportTemplate(fileName, fileDir, param, jdbcTemplate, response);
    }

    /**
     * 获取变量坐标和sql
     *
     * @param sheet1 sheet
     * @return 值
     */
    private static List<SqlPosition> getPositionAndSql(Sheet sheet1, Map<String, String> param) {
        List<SqlPosition> list = new ArrayList<>();
        int lastRowNum = sheet1.getLastRowNum();
        for (int i = 0; i <= lastRowNum; i++) {
            Row row = sheet1.getRow(i);
            if (row == null) {
                continue;
            }
            short lastCellNum = row.getLastCellNum();
            for (int j = 0; j < lastCellNum; j++) {
                Cell cell = row.getCell(j);
                if (cell == null) {
                    continue;
                }
                String cellStringValue = POIUtils.getCellStringValue(cell);
                if (cellStringValue.contains("$")) {
                    //替换参数
                    for (Map.Entry<String, String> temp : param.entrySet()) {
                        if (cellStringValue.contains(temp.getKey())) {
                            cellStringValue = cellStringValue.replace(temp.getKey(), temp.getValue());
                        }
                    }
                    int index = cellStringValue.indexOf("=");
                    if (index < 0) {//没有=号说明是变量
                        cell.setCellValue(cellStringValue);
                    } else {//sql的情况
                        String sql = cellStringValue.substring(index + 1);
                        SqlPosition sqlPosition = new SqlPosition();
                        List<CellStyle> headCellStyles = POIUtils.getRowCellStyles(row);
                        List<MergedRegion> rowMergedRegion = POIUtils.getRowMergedRegion(sheet1, row.getRowNum());
                        sqlPosition.setSql(sql)
                                .setRowIndex(cell.getRowIndex())
                                .setColumnIndex(cell.getColumnIndex())
                                .setCellStyles(headCellStyles)
                                .setMergedRegions(rowMergedRegion);
                        log.info("获取的坐标及sql信息:" + sqlPosition);
                        cell.setCellValue("");
                        list.add(sqlPosition);
                    }
                }
            }
        }
        return list;
    }

    /**
     * 表示将 sql 查询出来的结果集合，按照从左到右的方式进行填充
     */
    private static void fillRowData(Sheet sheet, Collection<Object> data, Row row, int offset, SqlPosition sqlPosition, boolean fisrt) {
        List<CellStyle> cellStyles = sqlPosition.getCellStyles();
        List<MergedRegion> mergedRegions = sqlPosition.getMergedRegions();
        List<Object> collect = new ArrayList<>(data);
        int beg = offset;
        outer:
        for (int i = 0; i < collect.size(); i++) {
            Object val = collect.get(i);
            Cell cell = row.getCell(offset);
            if (cell == null) {
                cell = row.createCell(offset);
            }
            if (val instanceof String) {
                cell.setCellType(Cell.CELL_TYPE_STRING);
                if ("".equals(val)) {
                    cell.setCellValue("");
                } else {
                    cell.setCellValue((String) val);
                }
            } else if (val instanceof BigDecimal) {
                cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                cell.setCellValue(((BigDecimal) val).doubleValue());
            }
            //下一列
            if (mergedRegions.size() > 0) {
                for (MergedRegion m : mergedRegions) {
                    if (m.getStartColumnIndex() == offset) {
                        if (!fisrt) {
                            CellRangeAddress region = new CellRangeAddress(row.getRowNum(), row.getRowNum(),
                                    m.getStartColumnIndex(), m.getEndColumnIndex());
                            sheet.addMergedRegion(region);
                            //设置合并单元格样式
                            for (int j = offset; j <= m.getEndColumnIndex(); j++) {
                                Cell c = row.getCell(j);
                                if (c == null) {
                                    c = row.createCell(j);
                                }
                                if (cellStyles != null) {
                                    c.setCellStyle(cellStyles.get(offset));
                                }
                            }
                        }
                        offset = m.getEndColumnIndex() + 1;
                        continue outer;
                    }
                }


            }
            //设置样式
            if (cellStyles != null && cellStyles.size()>offset) {
                cell.setCellStyle(cellStyles.get(offset));
            }
            offset++;
        }

    }

    /**
     * 浏览器下载excel
     *
     * @param fileName 文件名
     * @param wb       Workbook
     * @param response 流
     */
    public static void buildExcelDocument(String fileName, Workbook wb, HttpServletResponse response) {
        try {
            response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
            response.flushBuffer();
            wb.write(response.getOutputStream());
            /*File f = new File("D://temp//exp.xlsx");
            FileOutputStream out = new FileOutputStream(f);
            wb.write(out);
            out.close();*/
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

}

